/**
* <p>Title: DefaultExcelParser.java</p>
* <p>Description: IexcelParser实现类</p>
* <p>Company: www.huoyunren.com</p>
* @author duanxiangyun
* @date 2015年6月1日
* @version 1.0
*/
package com.duan.test.utils.Excel;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


/**
 * EXCEL操作实现类:与ExcelBean.java/IExcelParser.java合用
 */
public class DefaultExcelParser {
	
	
	private DefaultExcelParser() {}
	
	/***
	 * 创建excel解析器
	 * @param excelFileName 	excel文件名字
	 */
	public static IExcelParser createExcelParser(String excelFileName) {
		//先校验文件是否合法
		if(!excelFileName.endsWith(".xls") && !excelFileName.endsWith(".xlsx"))
			throw new IllegalArgumentException(excelFileName + " is not a valid excel file or not exist");
		
		return new DefaultParser(excelFileName);
	}
	
	private final static class DefaultParser implements IExcelParser{
		
		private final File f;
		private Workbook mWorkbook;
		
		DefaultParser(String file) {
			f = new File(file);
			try {
				mWorkbook = WorkbookFactory.create(new FileInputStream(f));
//				if (file.endsWith(".xls")) { 
//					mWorkbook = new HSSFWorkbook(new FileInputStream(f)); 
//			    }  
//		        else if (file.endsWith(".xlsx")) {  
//		        	mWorkbook = new XSSFWorkbook(new FileInputStream(f));  
//		        }  
			} catch (Exception e) {
				throw new RuntimeException(e.getMessage());
			} 
		}
		@Override
		public int getColNameIndex (String sheetName,String colname) {
			int index = 0;
			int colcount = getRow(getSheet(mWorkbook, sheetName), 1).getLastCellNum();
			for (int i = 1; i <= colcount; i++) {
				if (getCellValue(sheetName, 0, i-1).equals(colname)) {
					index = i;
					break;
				}
			}
			return index;
		}
		@Override
		public Workbook getWorkBook(String fileName) throws Exception {
			return WorkbookFactory.create(new FileInputStream(new File(fileName)));
		}

		@Override
		public Workbook getWorkBook() throws FileNotFoundException {
			return mWorkbook;
		}
		
		@Override
		public Sheet getSheet(Workbook wb, String sheetName) {
			
			return wb.getSheet(sheetName);
		}

		@Override
		public Sheet getSheet(Workbook wb, int sheetIndex) {
			
			return wb.getSheetAt(sheetIndex);
		}
		
		@Override
		public Row getRow(Sheet sheet, int rowNum) {
			return sheet.getRow(rowNum);
		}
		
		@Override
		public Row getRow(String fileName, String sheetName, int rowNum) throws Exception {
			Workbook wb = getWorkBook(fileName);
			Sheet sheet = getSheet(wb, sheetName);
			
			return getRow(sheet, rowNum);
		}
		
		@Override
		public String[] getRowValues(String sheetName, int rowIndex, boolean ignoreBlankCell) throws Exception {
			final Sheet sheet =getSheet(mWorkbook, sheetName);
			final int lastRowNum = sheet.getLastRowNum();
			
			if(rowIndex > lastRowNum)
				throw new IllegalArgumentException("the last row number is: " + lastRowNum 
						+ ", but the param is: " + rowIndex);
			
			final Row row = getRow(sheet, rowIndex);
			
			return getRowValues(row, ignoreBlankCell);
		}

		@Override
		public String[] getRowValues(Row row, boolean ignoreBlankCell) throws Exception {
			return getRowValues(row, 0, ignoreBlankCell);
		}

		@Override
		public String[] getRowValues(Row row, int startCellPos, boolean ignoreBlankCell) throws Exception {
			final int lastCellNum = row.getLastCellNum();
			if(startCellPos > lastCellNum)
				throw new IllegalArgumentException("the last cell number is: " + lastCellNum 
						+ ", but the param is: " + startCellPos);
			
			if(startCellPos < 0) startCellPos = 0;
			
			List<String> rowValuesList = new ArrayList<String>();
			for (int i = startCellPos; i < lastCellNum; i++) {
				Cell cell = row.getCell(i);
				if(cell == null) continue;
				
				String cellValue = getCellValue(cell);
				if("".endsWith(cellValue)) {
					if(!ignoreBlankCell) {
						rowValuesList.add(cellValue);
					} 
				}
				else {
					rowValuesList.add(cellValue);
				}
			}
			
			return rowValuesList.toArray(new String[rowValuesList.size()]);
		}

		@Override
		public String getCellValue(Cell cell) {
			String cellValue = "";
			
			if(cell == null)
				return cellValue;
			
			switch (cell.getCellType()) {
			
			case Cell.CELL_TYPE_STRING:
				//如果遇到自定义格式时间 可以通过加单引号的方式强制转为字符串
				cellValue = cell.getStringCellValue();
				break;
			case Cell.CELL_TYPE_FORMULA:
				FormulaEvaluator evaluator = mWorkbook.getCreationHelper().createFormulaEvaluator();
				evaluator.evaluateFormulaCell(cell);
				cell.setCellType (Cell.CELL_TYPE_STRING);
				cellValue = cell.getStringCellValue();
				break;	
			case HSSFCell.CELL_TYPE_BOOLEAN:
				cellValue = String.valueOf(cell.getBooleanCellValue());
	            break;
			case Cell.CELL_TYPE_NUMERIC:
				 if (HSSFDateUtil.isCellDateFormatted(cell)) { //Excel Date类型处理  
				        Date date = HSSFDateUtil.getJavaDate(cell.getNumericCellValue());     
				        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");  
				        cellValue = sdf.format(date);   
				 }
				 else {
//					 cell.setCellType (Cell.CELL_TYPE_STRING);
//					 cellValue = cell.getStringCellValue();
					 DecimalFormat decimalFormat = new DecimalFormat("###################.#################");  
					 cellValue = decimalFormat.format(cell.getNumericCellValue());
				 }				
//				cellValue = String.valueOf(cell.getNumericCellValue());
				break;
			case Cell.CELL_TYPE_BLANK:
				
				break;
				
			default:
				break;
			}
			
			return cellValue;
		}

		@Override
		public String getCellValue(String sheetName, int rowNumber, int cellNumber) {
			Sheet sheet = getSheet(mWorkbook, sheetName);
			final int lastRowNum = sheet.getLastRowNum();
			
			if(rowNumber > lastRowNum)
				throw new IllegalArgumentException("the last row number is: " + lastRowNum 
						+ ", but the param is: " + rowNumber);
			
			Row row = getRow(sheet, rowNumber);
			final int lastCellNum = row.getLastCellNum();
			
			if(cellNumber > lastCellNum)
				throw new IllegalArgumentException("the last cell number is: " + lastCellNum 
						+ ", but the param is: " + cellNumber);
			Cell cell = row.getCell(cellNumber);
			return cell != null? getCellValue(cell) : null;
		}
		
		@Override
		public String getCellValue(Sheet sheet, int rowNumber, int cellNumber) {
			final int lastRowNum = sheet.getLastRowNum();			
			if(rowNumber > lastRowNum)
				throw new IllegalArgumentException("the last row number is: " + lastRowNum 
						+ ", but the param is: " + rowNumber);
			
			Row row = getRow(sheet, rowNumber);
			if (row == null) {
			     row = sheet.createRow(rowNumber);
			}
//			final int lastCellNum = row.getLastCellNum();
//			
//			if(cellNumber > lastCellNum)
//				throw new IllegalArgumentException("the last cell number is: " + lastCellNum 
//						+ ", but the param is: " + cellNumber);
			Cell cell = row.getCell(cellNumber);			
			return cell != null? getCellValue(cell) : null;
		}
		
		@Override
		public int getValidRowsCount(String sheetName) {
			throw new UnsupportedOperationException();
		}

		@Override
		public List<String[]> getSheetDatas(String sheetName, boolean ignoreBlankCell) throws Exception {
			List<String[]> rowValuesList = new ArrayList<String[]>();
			
			Sheet sheet = getSheet(mWorkbook, sheetName);
			final int lastRowNum = sheet.getLastRowNum();

			for (int i = 0; i < lastRowNum; i++) {
				Row row = sheet.getRow(i);
				if(row != null){
					String[] rowValues = getRowValues(row, ignoreBlankCell);
					rowValuesList.add(rowValues);
				}
			}
			
			return rowValuesList;
		}
		
		@Override
		public Sheet deleteEptRows(Sheet sheet) {
			int i = sheet.getLastRowNum();
			Row tempRow;
			while(i > 0){			 
				 tempRow = sheet.getRow(i);
				 if(tempRow == null){
					 sheet.shiftRows(i+1, sheet.getLastRowNum()+1, -1);  
				 }
				 else {
					 Boolean bol = true;
					 for1:
					 for (int j = 0; j <= tempRow.getLastCellNum(); j++) {						 
						 if(getCellValue(sheet, i, j) != null && !getCellValue(sheet, i, j).trim().isEmpty()) {
							 bol = false;
							 break for1;
						 }
					 }
					 if (bol) {
					     sheet.shiftRows(i+1, sheet.getLastRowNum()+1, -1); 
					 }
				 }
				 i--;
			}
			return sheet;
		}
		
		@Override
	    public void writeInTemplate(String sheetName, String newContent, int beginRow, int beginCell)  
	    {    
			Sheet sheet = getSheet(mWorkbook, sheetName);
//	        HSSFRow  row  = (HSSFRow) sheet.getRow(beginRow);   
	        Row  row  = sheet.getRow(beginRow); 
	        if(null == row ){  
	            //如果不做空判断，你必须让你的模板文件画好边框，beginRow和beginCell必须在边框最大值以内  
	            //否则会出现空指针异常  
	            row = sheet.createRow(beginRow);  
	        }  
//	        HSSFCell   cell   = row.getCell(beginCell);
	        Cell   cell   = row.getCell(beginCell);
	        if(null == cell){  
	            cell = row.createCell(beginCell);  
	        }  
	        //设置存入内容为字符串  
	        cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
	        //向单元格中放入值  
	        cell.setCellValue(newContent);      
	    }  
	}
}
